package org.yak.core.provider;


import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.context.ContextLoaderListener;
import org.yak.core.exception.DaoException;
import org.yak.core.nosql.MongoDBConfig;
import org.yak.core.utils.JsonUtils;
import org.yak.core.utils.UUIDUtils;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

/**
 *
 * @平台：FAMSUN统一开发平台
 * @文件名称：SqlProvider.java
 * @文件描述：
 * @author xq
 * @日期：2017年11月20日上午9:41:51
 *
 */
public class SqlProvider {

	private static final Logger logger = Logger.getLogger(SqlProvider.class);
	
	/**
	 * 数据库TIMESTAMP 字段值
	 */
	private static final String TIMESTAMP_TIME = "CURRENT_TIMESTAMP";
	
	/**
	 * 字段下拉
	 */
	public static final String FIELD_SELECT = "select";
	
	/**
	 * 字段下拉选择
	 */
	public static final String FIELD_SELECT_CHOOSE = "select_choose";
	
	/**
	 * 文件上传字段
	 */
	public static final String FIELD_FILE = "file";

	/**
	 * 树
	 */
	public static final String FIELD_TREE = "tree";

	/**
	 * 字段复选框
	 */
	public static final String FIELD_CHECK = "checkbox";

	/**
	 * 日期格式
	 */
	public static final String FIELD_DATE = "date";

	/**
	 * 密码
	 */
	public static final String FIELD_PASSWORD = "password";

	/**
	 * 年月日日期格式
	 */
	public static final String DATEFOR_YEAR_MONTH_DAY = "Y-m-d";
	
	private static JdbcTemplate jdbcTemplate = (JdbcTemplate)ContextLoaderListener.getCurrentWebApplicationContext().getBean("jdbcTemplate");
	
	/**
	 * 数据库类型MySQL Oracle Microsoft
	 */
	private static String dbDriver;
	
	/**
	 * mysql
	 */
	public static final String DBDRIVER_MYSQL = "MySQL";
	
	/**
	 * oracle
	 */
	public static final String DBDRIVER_ORACLE = "Oracle";
	
	/**
	 * sqlserver
	 */
	public static final String DBDRIVER_SQLSERVER = "Microsoft";
	
	//定义临时表名称列表，防止重复
	private List<String> tempTableNameList = new ArrayList<String>();
	
	/**
	 * MONGODB数据库名称
	 */
	public static final String MONGODB_NAME = "bkfm";
	
	/**
	 * 缓存数据业务模型表名
	 */
	public static final String MONGODB_TABLEMETADATA_NAME = "blk_table_metadata";
	
	
	static {
		getDBType();
	}
	/**
	 * 查询业务模型数据结构
	 * @param obj_path
	 * @return
	 * @throws Exception
	 */
	private List<Map<String,Object>> getTableMetaColumn(String obj_path)throws Exception{
    	/*StringBuffer sql = new StringBuffer();
    	sql.append("SELECT * FROM BLK_TABLE_METADATA A WHERE EXISTS (SELECT B.TABLEMETADATA_ID  FROM BLK_TABLE_METADATA B where B.OBJ_PATH ='").append(obj_path).append("' AND A.FATHER_TABLEMETADATA_ID = B.TABLEMETADATA_ID) ORDER BY A.ORDER_BY ASC");
    	List<Map<String,Object>> fields = jdbcTemplate.queryForList(sql.toString());
    	if(fields == null || fields.isEmpty()){
    		throw new DaoException("操作作表："+obj_path+"异常,没有找到对应的数据字段");
    	}
    	return fields;*/
		Map<String,Object> queryFilter = new HashMap<String,Object>();
		queryFilter.put("obj_path", obj_path);
		List<Map<String,Object>> fields = MongoDBConfig.queryMapList(MONGODB_NAME, MONGODB_TABLEMETADATA_NAME, queryFilter);
		if(fields == null || fields.isEmpty()) {
			return null;
		}
		Map<String,Object> param = fields.get(0);
		queryFilter.clear();
		queryFilter.put("father_tablemetadata_id", param.get("TABLEMETADATA_ID"));
		fields = MongoDBConfig.queryMapList(MONGODB_NAME, MONGODB_TABLEMETADATA_NAME, queryFilter);
		if(fields == null || fields.isEmpty()) {
			return null;
		}
		return fields;
	}
	
	/**
	 * 生成不重复的临时表名称
	 * @return
	 */
	private String getTempTableName() {
		String table_name = String.valueOf(System.currentTimeMillis()+UUIDUtils.getRandom100());
		if(tempTableNameList.contains(table_name)) {
			table_name =  getTempTableName();
		}else {
			tempTableNameList.add(table_name);
		}
		return table_name;
	}
	/**
	 * 查询业务模型表数据
	 * @param paramValue
	 * @return
	 * @throws Exception
	 */
	public String selectTableDataSqlProvider(final ProviderSqlParam sqlParam)throws Exception{
		if(sqlParam == null || StringUtils.isEmpty(sqlParam.getObj_path())) {
			throw new DaoException("数据格式不合法，表名无效");
		}
		
		//获取业务表名称
		String table_name = sqlParam.getObj_path().substring(sqlParam.getObj_path().lastIndexOf("/")+1).toUpperCase();
		StringBuffer sql = new StringBuffer("SELECT RWID,CREATE_USER_ID,CREATE_USER_NAME,").append(formatDate_field("CREATE_DATE")).append(",UPDATE_USER_ID,UPDATE_USER_NAME,").append(formatDate_field("UPDATE_DATE")).append(",OWNER_USER_ID,OWNER_ORG_ID,").append(formatDate_field("TIME_STAMP "));
		List<Map<String,Object>> fieldList = getTableMetaColumn(sqlParam.getObj_path());
		if(fieldList==null || fieldList.isEmpty()) {
			throw new DaoException("没有查询到需要操作的字段内容，请联系管理员分配权限或者刷新缓存");
		}
		StringBuffer leftJoinSql = new StringBuffer();
		for(Map<String,Object> field : fieldList) {
			//sql.append(",").append(field.get("EN_US"));
			Object columnName = field.get("EN_US");
			Object userConfig = field.get("USER_CONFIG");
			if(!org.springframework.util.StringUtils.isEmpty(userConfig)){
				JSONObject userConfigJson = JSONObject.fromObject(userConfig);
				if(!userConfigJson.containsKey("ftype")){
					//直接查询字段
					sql.append(",").append(columnName);
					continue;
				}
				String ftype = userConfigJson.getString("ftype");
				if(FIELD_SELECT.equalsIgnoreCase(ftype) || FIELD_SELECT_CHOOSE.equalsIgnoreCase(ftype) || FIELD_FILE.equalsIgnoreCase(ftype)){
					//下拉选项
					sql.append(",").append(columnName);
					//下拉
					Object selOptions = field.get("SEL_OPTIONS");
					String tempTableName = "T_"+getTempTableName();
					String tempChildTableName = "TM_"+getTempTableName();
					sql.append(",").append(tempTableName).append(".DISPLAYVALUE").append(" AS ").append(columnName).append("_DISPLAYVALUE");

					leftJoinSql.append(" LEFT JOIN (").append(parseLeftJoinSql(selOptions,tempChildTableName)).append(")").append(tempTableName).append(" ON ").append(table_name)
					.append(".").append(columnName).append("=").append(tempTableName).append(".VALUE");
				}else if(FIELD_TREE.equalsIgnoreCase(ftype)){
					//树
					sql.append(",").append(columnName);
					
					//树
					String mappingSql = TreeConfigCache.getTreeConfigMappingSql(userConfigJson.getString("treeConfigId"));
					String tempTableName = "TR_"+getTempTableName();
					sql.append(",").append(tempTableName).append(".DISPLAYVALUE").append(" AS ").append(columnName).append("_DISPLAYVALUE");
					leftJoinSql.append(" LEFT JOIN (").append(mappingSql).append(") ").append(tempTableName).append(" ON ").append(table_name).append(".")
					.append(columnName).append("=").append(tempTableName).append(".VALUE");
				}else {
					sql.append(",").append(columnName);
				}
			}else {
				sql.append(",").append(columnName);
			}

		}
		sql.append(" FROM ").append(table_name).append(" ").append(table_name).append(leftJoinSql);
		//设置查询条件
		if(!StringUtils.isEmpty(sqlParam.getFilter())) {
			sql.append(" WHERE ").append(sqlParam.getFilter());
		}
		if(!StringUtils.isEmpty(sqlParam.getOrder_by())) {
			sql.append(" ").append(sqlParam.getOrder_by());
		}else {
			//默认按照时间戳倒序
			sql.append(" ORDER BY TIME_STAMP DESC");
		}
		return sql.toString();
	}
	
	/**
	 * 新增sql语句
	 * @param sqlParam
	 * @return
	 * @throws Exception
	 */
	public String insertTableDataSqlProvider(final ProviderSqlParam sqlParam)throws Exception{
		if(sqlParam == null || StringUtils.isEmpty(sqlParam.getObj_path())) {
			throw new DaoException("数据格式不合法，表名无效");
		}
		//获取业务表名称
		String table_name = sqlParam.getObj_path().substring(sqlParam.getObj_path().lastIndexOf("/")+1).toUpperCase();
		Map<String,Object> valueMap = sqlParam.getTableMetaDataValue();
		validateTableMetaDataValue(valueMap);
		StringBuffer sql = new StringBuffer("INSERT INTO ").append(table_name).append("(RWID,CREATE_USER_ID,CREATE_USER_NAME,CREATE_DATE,OWNER_USER_ID,OWNER_ORG_ID,TIME_STAMP");
		StringBuffer valueSql = new StringBuffer(" VALUES (#{tableMetaDataValue.RWID},#{tableMetaDataValue.CREATE_USER_ID},#{tableMetaDataValue.CREATE_USER_NAME},#{tableMetaDataValue.CREATE_DATE},#{tableMetaDataValue.OWNER_USER_ID},#{tableMetaDataValue.OWNER_ORG_ID},").append(TIMESTAMP_TIME);
		List<Map<String,Object>> fieldList = getTableMetaColumn(sqlParam.getObj_path());
		if(fieldList==null || fieldList.isEmpty()) {
			throw new DaoException("没有查询到需要操作的字段内容，请联系管理员分配权限或者刷新缓存");
		}
		for(Map<String,Object> field : fieldList) {
			Object column_name = field.get("EN_US");
			if(valueMap.containsKey(column_name)) {
				sql.append(",").append(column_name);
				
				if(org.springframework.util.StringUtils.isEmpty(valueMap.get(column_name))) {
					valueSql.append(",#{tableMetaDataValue.").append(column_name).append(",jdbcType=NULL}");
				}else {
					if(field.get("MODEL_FIELD").equals("5")) {
						//clob字段
						valueSql.append(",#{tableMetaDataValue.").append(column_name).append(",jdbcType=CLOB}");
					}else {
						valueSql.append(",#{tableMetaDataValue.").append(column_name).append("}");
					}
				}
			}
		}
		sql.append(") ").append(valueSql).append(")");
		
		
		return sql.toString();
	}
	
	public String updateTableDataSqlProvider(final ProviderSqlParam sqlParam)throws Exception{
		if(sqlParam == null || StringUtils.isEmpty(sqlParam.getObj_path())) {
			throw new DaoException("数据格式不合法，表名无效");
		}
		//获取业务表名称
		String table_name = sqlParam.getObj_path().substring(sqlParam.getObj_path().lastIndexOf("/")+1).toUpperCase();
		Map<String,Object> valueMap = sqlParam.getTableMetaDataValue();
		validateTableMetaDataValue(valueMap);
		StringBuffer sql = new StringBuffer("UPDATE ").append(table_name).append(" SET UPDATE_USER_ID = #{tableMetaDataValue.UPDATE_USER_ID},UPDATE_USER_NAME = #{tableMetaDataValue.UPDATE_USER_NAME},UPDATE_DATE = #{tableMetaDataValue.UPDATE_DATE},TIME_STAMP=").append(TIMESTAMP_TIME);
		List<Map<String,Object>> fieldList = getTableMetaColumn(sqlParam.getObj_path());
		if(fieldList==null || fieldList.isEmpty()) {
			throw new DaoException("没有查询到需要操作的字段内容，请联系管理员分配权限或者刷新缓存");
		}
		for(Map<String,Object> field : fieldList) {
			Object column_name = field.get("EN_US");
			if(valueMap.containsKey(column_name)) {
				if(org.springframework.util.StringUtils.isEmpty(valueMap.get(column_name))) {
					//空值
					sql.append(",").append(column_name).append(" = #{tableMetaDataValue.").append(column_name).append(",jdbcType=NULL}");
				}else {
					if(field.get("MODEL_FIELD").equals("5")) {
						//clob字段
						sql.append(",").append(column_name).append(" = #{tableMetaDataValue.").append(column_name).append(",jdbcType=CLOB}");
					}else {
						sql.append(",").append(column_name).append(" = #{tableMetaDataValue.").append(column_name).append("}");
					}
					
				}
			}
		}
		sql.append(" WHERE RWID = ").append("#{tableMetaDataValue.RWID}");
		
		
		return sql.toString();
	}
	
	public String deleteTableDataSqlProvider(final ProviderSqlParam sqlParam)throws Exception{
		if(sqlParam == null || StringUtils.isEmpty(sqlParam.getObj_path())) {
			throw new DaoException("数据格式不合法，表名无效");
		}
		//获取业务表名称
		String table_name = sqlParam.getObj_path().substring(sqlParam.getObj_path().lastIndexOf("/")+1).toUpperCase();
		StringBuffer sqlStr = new StringBuffer("DELETE FROM ").append(table_name);
		if(!StringUtils.isEmpty(sqlParam.getFilter())) {
			sqlStr.append(" WHERE ").append(sqlParam.getFilter());
		}else {
			throw new DaoException("无效的删除操作，缺少删除主键或者删除条件:"+sqlStr);
		}
		return sqlStr.toString();
	}
	
	/**
	 * 验证valueMap值
	 * @param valueMap
	 * @return
	 */
	private boolean validateTableMetaDataValue(Map<String,Object> valueMap) {
		if(valueMap == null || valueMap.isEmpty()) {
			throw new DaoException("数据格式不合法，无效的插入记录");
		}
		if(!valueMap.containsKey("RWID")) {
			throw new DaoException("数据格式不合法，无效的RWID");
		}
		return true;
	}
	
	/**
	 * 格式化leftjoin语句
	 * @param mainTable 主表名称
	 * @param fieldColumnMap
	 * @return
	 */
	public String parseLeftJoinSql(Object selOptions,String tempTableName){
		if(org.springframework.util.StringUtils.isEmpty(selOptions)){
			return "";
		}

		JSONObject selJson = JSONObject.fromObject(selOptions).getJSONObject("RangConfig");
		int configType = selJson.getInt("configType");
		StringBuffer childSql = new StringBuffer();
		//子查询表别名
		
		switch(configType){
			case 0:{
				break;
			}
			case 1:{
				JSONArray configArray = selJson.getJSONArray("Config");
				childSql.append("SELECT * FROM (");
				for(int i=0;i<configArray.size();i++){
					JSONObject obj = configArray.getJSONObject(i);
					String key = JsonUtils.getSingJsonKey(obj);
					String value = obj.getString(key);
					if(i==0){
						childSql.append("SELECT '").append(key).append("' AS VALUE,'").append(value).append("' AS DISPLAYVALUE");
						if(DBDRIVER_ORACLE.equals(dbDriver)) {
							childSql.append(" FROM DUAL ");
						}
					}else{
						childSql.append(" UNION ")
						.append("SELECT '").append(key).append("' AS VALUE,'").append(value).append("' AS DISPLAYVALUE");
						if(DBDRIVER_ORACLE.equals(dbDriver)) {
							childSql.append(" FROM DUAL ");
						}
					}
				}
				childSql.append(")").append(tempTableName);
				break;
			}
			case 2:{
				JSONObject sqlConfig = selJson.getJSONObject("Config");
				childSql.append(sqlConfig.get("Sql"));
				break;
			}
		}
		return childSql.toString();
	}
	
	/**
	 * 获取数据库类型
	 * @return
	 */
	private static String getDBType() {
		if(!StringUtils.isEmpty(dbDriver)) {
			return dbDriver;
		}else {
			try {//MySQL Connector Java, Oracle JDBC driver,Microsoft JDBC Driver 4.0 for SQL Server
				Connection con = jdbcTemplate.getDataSource().getConnection();
				dbDriver = StringUtils.split(con.getMetaData().getDriverName(), " ")[0];
				con.close();
				return dbDriver;
			}catch(Exception e) {
				logger.error("获取数据库");
				return null;
			}
		}
	}
	
	/**
	 * 格式化数据库日期字段
	 * @return
	 */
	private static String formatDate_field(String field_name) {
		String format_field = "";
		String dbType = getDBType();
		switch(dbType) {
			case DBDRIVER_MYSQL:{
				format_field = "DATE_FORMAT("+field_name+",'%Y-%m-%d %H:%i:%s') AS "+field_name;
				break;
			}
			case DBDRIVER_ORACLE:{
				format_field = "TO_CHAR("+field_name+",'YYYY-MM-DD HH24:MI:SS') AS "+field_name;
				break;
			}
			case DBDRIVER_SQLSERVER:{
				format_field = "CONVERT(VARCHAR, "+field_name+", 120 ) AS "+field_name;
				break;
			}
			
		}
		return format_field;
	}
}
